package com.jfunit.java.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfunit.java.helper.DatabaseHelper;
import com.jfunit.java.util.MapUtil;

public class FrameworkDAO {
    private static final Logger LOGGER = LoggerFactory.getLogger(FrameworkDAO.class);

    private static Map<String, ArrayList<String>> TABLE_COLUMNS_MAP = new HashMap<String, ArrayList<String>>();

    /***
     * 参数为实体类类型 该方法用于查询出该实体类对应的表中所有数据
     * 
     * @param cl
     * @return
     */
    public <T> List<T> getEntityList(Class<T> cl, String sql, Object... params) {
        List<T> result = null;
        try {
            ArrayList<String> col = getAllColumns(cl.getSimpleName());
            // 拼接sql语句
            StringBuffer sb = new StringBuffer();
            sb.append("select ");
            for (int i = 0; i < col.size(); i++) {
                sb.append(col.get(i));
                if (i != col.size() - 1) {
                    sb.append(" , ");
                }
            }
            sb.append(" from ");
            sb.append(cl.getSimpleName().toUpperCase());
            result = DatabaseHelper.getQueryRunner().query(DatabaseHelper.getConnection(), sb.toString(),
                    new BeanListHandler<T>(cl), params);
        } catch (Exception e) {
            LOGGER.error("baseDao query list failure", e);
            throw new RuntimeException(e);
        }
        return result;
    }

    /***
     * 该方法用于 根据表主键值 获取该实体类对应表 查询出该条信息
     * 
     * @param cl 实体类类型
     * @param id 主键id值
     * @return
     */
    public <T> T getEntityById(Class<T> cl, int id) {
        T result = null;
        try {
            ArrayList<String> col = getAllColumns(cl.getSimpleName());

            StringBuffer sb = new StringBuffer();
            sb.append("select ");
            for (int i = 0; i < col.size(); i++) {
                sb.append(col.get(i));
                if (i != col.size() - 1) {
                    sb.append(" , ");
                }
            }
            sb.append(" from ");
            sb.append(cl.getSimpleName().toUpperCase());
            sb.append(" where ");
            sb.append(col.get(0));
            sb.append(" = ? ");

            result = DatabaseHelper.getQueryRunner().query(DatabaseHelper.getConnection(), sb.toString(),
                    new BeanHandler<T>(cl), new Object[] { id });
        } catch (Exception e) {
            LOGGER.error("query entity failure", e);
            throw new RuntimeException(e);
        }
        return result;
    }

    /***
     * 该方法用于单条件 单表查询
     * 
     * @param cl 实体类类型
     * @param name 参数名
     * @param value 参数值
     * @return
     */
    public <T> List<T> getEntityByCondition(Class<T> cl, Map<String, Object> where) {
        List<T> result = null;
        try {
            ArrayList<String> col = getAllColumns(cl.getSimpleName());
            StringBuffer sb = new StringBuffer();
            sb.append("select ");
            for (int i = 0; i < col.size(); i++) {
                sb.append(col.get(i));
                if (i != col.size() - 1) {
                    sb.append(" , ");
                }
            }
            sb.append(" from ");
            sb.append(cl.getSimpleName().toUpperCase());
            sb.append(" where ");
            Object[] params = new Object[where.size()];
            int i = 0;
            for (Map.Entry<String, Object> entiry : where.entrySet()) {
                sb.append(entiry.getKey());
                sb.append(" = ? ");
                if (i < (where.size() - 1)) {
                    sb.append(" and ");
                }
                params[i] = entiry.getValue();
                i++;
            }
            result = DatabaseHelper.getQueryRunner().query(DatabaseHelper.getConnection(), sb.toString(),
                    new BeanListHandler<T>(cl), params);
        } catch (Exception e) {
            LOGGER.error("baseDao query getEntityByCondition failure", e);
            throw new RuntimeException(e);
        }
        return result;

    }

    /***
     * 该方法用于 模糊查询 全匹配
     * 
     * @param cl 实体类类型
     * @param name 参数名
     * @param value 参数值
     * @return
     */
    public <T> List<T> getEntityListByLike(Class<T> cl, String name, Object value) {
        List<T> result = null;
        try {
            ArrayList<String> col = getAllColumns(cl.getSimpleName());

            StringBuffer sb = new StringBuffer();
            sb.append("select ");
            for (int i = 0; i < col.size(); i++) {
                sb.append(col.get(i));
                if (i != col.size() - 1) {
                    sb.append(",");
                }
            }
            sb.append(" from ");
            sb.append(cl.getSimpleName().toUpperCase());
            sb.append(" where ");
            sb.append(name);
            sb.append(" like ?");
            result = DatabaseHelper.getQueryRunner().query(DatabaseHelper.getConnection(), sb.toString(),
                    new BeanListHandler<T>(cl), new Object[]{value});
        } catch (Exception e) {
            LOGGER.error("baseDao query getEntityByCondition failure", e);
            throw new RuntimeException(e);
        }
        return result;
    }

    public static List<Map<String, Object>> executorQuery(String sql, Object... params) {
        return DatabaseHelper.executorQuery(sql, params);
    }

    /***
     * 该方法用于万能插入数据
     * 
     * @param ob 实体对象
     * @return
     * @throws Exception 
     */
    public boolean insertByObject(Object obj) throws Exception {
        System.out.println(obj.toString());
        Map<String, Object> fieldMap = (Map<String, Object>) MapUtil.Obj2Map(obj);
        fieldMap.remove("id");
        if (MapUtils.isEmpty(fieldMap)) {
            LOGGER.error("can not insert entity: fieldMap is empty");
            return false;
        }
        String sql = "INSERT INTO " + obj.getClass().getSimpleName().toUpperCase();
        StringBuilder columns = new StringBuilder("(");
        StringBuilder values = new StringBuilder("(");

        for (String fieldName : fieldMap.keySet()) {
            columns.append(fieldName).append(",");
            values.append("?,");
        }

        columns.replace(columns.lastIndexOf(","), columns.length(), ")");
        values.replace(values.lastIndexOf(","), values.length(), ")");
        sql += columns + " VALUES " + values;
        Object[] params = fieldMap.values().toArray();
        return DatabaseHelper.executeUpdate(sql, params) == 1;
    }

    public static <T> boolean insertByMap(Class<T> entityClass, Map<String, Object> fieldMap) {
        return DatabaseHelper.insertEntiry(entityClass, fieldMap);
    }

    /***
     * 该方法用于插入数据并获取数据主键的ID值
     * 
     * @param ob 实体类对象
     * @return
     */
    public int insertGetGeneratedKey(Object ob) {
        return 0;
    }

    public boolean updateById(Object obj, int id) throws Exception {
        Map<String, Object> whereMap = new HashMap<String, Object>();
        whereMap.put("id", id);
        return this.updateByObject(obj, whereMap);
    }

    /***
     * 该方法用于更新数据
     * 
     * @param ob 实体类对象（必须有主键id）
     * @return
     * @throws Exception 
     */
    public boolean updateByObject(Object obj, Map<String, Object> where) throws Exception {
        Map<String, Object> fieldMap = (Map<String, Object>) MapUtil.Obj2Map(obj);
        if (MapUtils.isEmpty(fieldMap)) {
            LOGGER.error("can not update entity: fieldMap is empty");
            return false;
        }

        if (MapUtils.isEmpty(where)) {
            LOGGER.error("can not update entity: where is empty");
            return false;
        }

        String sql = "update " + obj.getClass().getSimpleName().toUpperCase() + " SET ";
        StringBuilder columns = new StringBuilder();
        for (String fieldName : fieldMap.keySet()) {
            columns.append(fieldName).append("=?,");
        }
        sql += columns.substring(0, columns.lastIndexOf(",")) + " WHERE ";
        StringBuilder strWhere = new StringBuilder();
        for (String fieldName : where.keySet()) {
            strWhere.append(" ").append(fieldName).append("=? and");
        }

        sql += strWhere.substring(0, strWhere.lastIndexOf("and"));

        List<Object> paramList = new ArrayList<Object>();
        paramList.addAll(fieldMap.values());
        paramList.addAll(where.values());
        Object[] params = paramList.toArray();
        return DatabaseHelper.executeUpdate(sql, params) == 1;
    }
    
    public <T> boolean updateMapById(Class<T> entityClass, long id, Map<String, Object> fieldMap) {
        return DatabaseHelper.updateEntity(entityClass, id, fieldMap);

    }

    

    public static int executeUpdate(String sql, Object... params) {
        return DatabaseHelper.executeUpdate(sql, params);
    }

    /***
     * 该方法用于删除数据
     * 
     * @param cl 删除实体类类型
     * @param id 主键编号
     * @return
     */
    public <T> boolean delete(Class<T> cl, int id) {
        String sql = "delete from " + cl.getSimpleName() + " where id = ?";
        return DatabaseHelper.executeUpdate(sql, id) == 1;

    }

    /***
     * 该方法用于 所有查询操作 注意一点 尽量保证 连接的表中数据 除了主外键关联的以外 尽量不要有重复的表列名 参数1：查询出来的数据 放到哪个 实体类中 参数2：查询的sql语句
     * 参数3：所有的条件的值
     * 
     * @return
     */
    public List<Map<String, Object>> getListBySql(String sql, Object[] obs) {
        return DatabaseHelper.executorQuery(sql, obs);
    }

    /***
     * 通过数据库元对象获取表中列的所有属性（名称，类型，长度等）
     * 
     * @param tableName
     * @return
     */
    public ArrayList<String> getAllColumns(String tableName) {
        if (TABLE_COLUMNS_MAP.containsKey(tableName)) {
            return TABLE_COLUMNS_MAP.get(tableName);
        }
        ArrayList<String> ar = new ArrayList<String>();
        Connection conn = DatabaseHelper.getConnection();
        ResultSet colrs = null;
        try {
            // 数据库元数据对象，也可以理解为 数据库数据对象
            DatabaseMetaData dbm = conn.getMetaData();
            // 通过表名获取到 存有所有列信息的结果集对象
            colrs = dbm.getColumns(null, "%", tableName, "%");
            while (colrs.next()) {
                String columnName = colrs.getString("COLUMN_NAME");
                String columnType = colrs.getString("TYPE_NAME");
                int datasize = colrs.getInt("COLUMN_SIZE");
                int digits = colrs.getInt("DECIMAL_DIGITS");
                int nullable = colrs.getInt("NULLABLE");
                ar.add(columnName);
            }
            TABLE_COLUMNS_MAP.put(tableName, ar);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseHelper.closeRes(null, colrs);
        }
        return ar;
    }

    public static void beginTransaction(){
        DatabaseHelper.beginTransaction();
    }
    
    public static void commitTransaction(){
        DatabaseHelper.commitTransaction();
    }
    public static void rollbackTransaction(){
        DatabaseHelper.rollbackTransaction();
    }
}
